<?php

namespace app\index\controller;
use think\Db as portraitDB;

class InstitutionalCooperationPortrait extends Common{
    public $datas;

    /**
     * 机构合作水平
     */
    public function getInstitutionalCooperation(){
        try {
            $sql = "SELECT cooperation_out cooperationOut,cooperation_in cooperationIn,in_degree inDegree,out_degree outDegree FROM institutional_cooperation;";
            $result = portraitDB::query($sql);

            if(empty($result)){
                $this->returnMsg(1, '暂无数据！');
            }

            $this->returnMsg(1, '获取数据成功！', $result);
        }catch (Exception $e){
            echo $e->getMessage();
        }
    }


    /**
     * 对外合作关系
     */
    public function getForeignCooperation(){
        try {
            $this->datas = $this->params;
            $max = 50;
            if(!empty($this->datas['count'])){
                $max = $this->datas['count'];
            }
            $sql = "SELECT agency,cooperation FROM foreign_cooperation limit ".$max;
            $result = portraitDB::query($sql);

            if(empty($result)){
                $this->returnMsg(1, '暂无数据！');
            }

            $this->returnMsg(1, '获取数据成功！', $result);
        }catch (Exception $e){
            echo $e->getMessage();
        }
    }


    /**
     * 合作信息筛选
     */
    public function getCooperationInformation(){
        $this->datas = $this->params;

        $condition = "";
        if(!empty($this->datas['resourceCode'])){
            $condition .= " AND resourceCode = '".$this->datas['resourceCode']."' ";
        }
        if(!empty($this->datas['yearFrom']) && !empty($this->datas['yearTo'])){
            $condition .= " AND year >= '".$this->datas['yearFrom']."' AND year <= '".$this->datas['yearTo']."' ";
        }
        if(!empty($this->datas['secondaryCollege'])){
            $condition .= " AND secondaryCollege = '".$this->datas['secondaryCollege']."' ";
        }
        if(!empty($this->datas['creator'])){
            //$condition .= " AND creator = '".$this->datas['creator']."' ";
            $condition .= " AND creatorStandard like '%".$this->datas['creator']."%' ";
        }

        try {
            //成果总数
            $sql = "SELECT COUNT(1) resultSum FROM resource_collection WHERE 1=1 ".$condition;
            $resultSum = portraitDB::query($sql);


            if(empty($resultSum[0]['resultSum'])){
                $this->returnMsg(1, '暂无数据！');
            }

            //合作成果
            $sql = "SELECT COUNT(distinct a.id) cooperationResults from resource_collection a where "
                ."a.id not in (select resource_id from resource_cooperate_info b) ".$condition;
            $cooperationResults = portraitDB::query($sql);

            //合作次数
            $sql = "SELECT b.partner partner,COUNT(b.partner) cooperateCount FROM resource_collection a "
                ."RIGHT JOIN resource_cooperate_info b ON a.id = b.resource_id WHERE 1=1 ".$condition
                ."GROUP BY b.partner";
            $cooperateCount = portraitDB::query($sql);

            //合作地域分布
            $sql = "SELECT b.area area,COUNT(b.area) areaCount FROM resource_collection a "
                ."RIGHT JOIN resource_cooperate_info b ON a.id = b.resource_id WHERE 1=1 ".$condition
                ."GROUP BY b.area";
            $areaCount = portraitDB::query($sql);

            //合作成果时间分布
            $sql = "SELECT year,COUNT(resourceCode) amount,resourceCode FROM resource_collection WHERE 1=1 ".$condition
                ."GROUP BY year,resourceCode ORDER BY resourceCode";
            $cooperateResultCount = portraitDB::query($sql);


            $pageNo = 1;
            $pageSize = 10;

            if(!empty($this->datas['pageNo'])){
                $pageNo = $this->datas['pageNo'];
            }

            if(!empty($this->datas['pageSize'])){
                $pageSize = $this->datas['pageSize'];
            }

            $pageNo = ($pageNo - 1) * $pageSize;


            //成果明细
            $sql = "SELECT title,creator,journal,create_time createTime FROM resource_collection WHERE 1=1 ".$condition
                ." limit ".$pageNo.",".$pageSize;
            $pageRes = portraitDB::query($sql);
            $sql = "SELECT COUNT(1) num FROM resource_collection WHERE 1=1 ".$condition;
            $countRes = portraitDB::query($sql);

            $returnResult = [];
            $returnResult['resultSum'] = $resultSum;
            $returnResult['cooperation'] = $cooperationResults;
            $returnResult['cooperateCount'] = $cooperateCount;
            $returnResult['area'] = $areaCount;
            $returnResult['cooperateResult'] = $cooperateResultCount;
            $returnResult['tableData'] = $pageRes;
            $returnResult['tableTotal'] = $countRes[0]['num'];

            $this->returnMsg(1, '获取数据成功！', $returnResult);
        }catch (Exception $e){
            echo $e->getMessage();
        }
    }
}